package com.corgi.core.modules.sys.repository;

import com.corgi.core.modules.sys.entity.TestRank;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * TestRankRepository
 *
 * @title: TestRankRepository
 * @description:
 * @author: dengmiao
 * @create: 2020-05-23 14:59
 **/
public interface TestRankRepository extends JpaRepository<TestRank, Integer> {

    /*
    SELECT
	t.id,
	t.uid,
	t.score,
CASE

		WHEN @rowtotal = t.score THEN
		@rownum
		WHEN @rowtotal := t.score THEN
		@rownum := @rownum + 1
		WHEN @rowtotal = 0 THEN
		@rownum := @rownum + 1
	END AS rank
FROM
	( SELECT id, uid, score FROM test_rank ORDER BY score DESC ) AS t,
	( SELECT @rownum := 0, @rowtotal := NULL ) r
     */
    // 别名注入失败
    @Query(value = "SELECT\n" +
            "\tt.id,\n" +
            "\tt.uid,\n" +
            "\tt.score,\n" +
            "CASE\n" +
            "\t\t\n" +
            "\t\tWHEN @rowtotal = t.score THEN\n" +
            "\t\t@rownum \n" +
            "\t\tWHEN @rowtotal \\:= t.score THEN\n" +
            "\t\t@rownum \\:= @rownum + 1 \n" +
            "\t\tWHEN @rowtotal = 0 THEN\n" +
            "\t\t@rownum \\:= @rownum + 1 \n" +
            "\tEND AS rank \n" +
            "FROM\n" +
            "\t( SELECT id, uid, score FROM test_rank ORDER BY score DESC ) AS t,\n" +
            "\t( SELECT @rownum \\:= 0, @rowtotal \\:= NULL ) r", nativeQuery = true)
    List<TestRank> getRank();
}
